﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class T_ImportPage : System.Web.UI.Page
{
    public static string accnum;           //登录账号
    public static string role;             //登录身份

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            accnum = (string)Session["AccNum"];
            role = (string)Session["role"];

            DL1.Text = "请选择导出数据";
            DL2.Text = "请选择学期";
            DL2.Visible = false;

            DL3.Text = "请选择删除数据";
            DL4.Text = "请选择学期";
            DL4.Visible = false;
        }
    }

    protected void btnExport_Click(object sender, EventArgs e)//丛excel导入到数据库中
    {
        string filepath = this.fileUpload.PostedFile.FileName;
        ExcelToSQL ets = new ExcelToSQL();
        /*该部分获取的是文件名，没有路径，所以很有可能找不到该excel文件，所以如果有问题便可以这样写：   
        string fileName = fileUpload.PostedFile.FileName;          
         fileUpload.PostedFile.SaveAs(Server.MapPath("upfile") + "\\" + fileName);          
        string filepath = Server.MapPath("upfile") + "\\" + fileName; */
        if (filepath != "")
        {
            //if (this.txtTableName.Text != "")
            //{
                if (filepath.Contains("xls"))
                {
                    int result = ets.ImportSql(filepath, "CLASS_INFO"); //this.txtTableName.Text获得数据库表名，如"student"
                    if (result == (int)ExcelToSQL.ImportState.tableNameError)
                        ets.Alert("此表名在数据中不存在！");
                    else if (result == (int)ExcelToSQL.ImportState.excelFormatError)
                        ets.Alert("Excel格式不能正确读取！");
                    else if (result == (int)ExcelToSQL.ImportState.fieldMatchError)
                        ets.Alert("Excel里的字段和Sql Server里的字段不匹配！");
                    else if (result == (int)ExcelToSQL.ImportState.dataTypeError)
                        ets.Alert("转换数据类型时发生错误!");
                    else if (result == (int)ExcelToSQL.ImportState.right)
                    {
                        ets.Alert("导入成功");
                    }
                }
                else ets.Alert("上传的文件类型必须为excel文件！");
            //}
            //else ets.Alert("表名不能为空！");
        }
        else ets.Alert("没有选择要上传的文件！");
    }


    protected void Button1_Click(object sender, EventArgs e) //Button1_Click函数响应事件，从数据表中导出数据，表名从文本框TextBox1中获得
    {
        DataTableToExcel dt = new DataTableToExcel();
        string tableName = "CLASS_INFO";
        string str_sql = "";
        /*缺少错误处理，表名不存在的情况*/
        /*此处以STU_INFO表明为例，尚未处理导出数据0712617到excel中没有‘0’的问题*/
        if (DL1.Text == "请选择导出数据")
        {
            Alert("请选择导出数据");
        }
        else
        {
            if (DL1.Text == "全部导出")
            {
                str_sql = "select * from CLASS_INFO,COURSE_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='"+accnum+"';";
                string column_names = "semesterid,courseid,studentid,mark,state"; 
                DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
            else if (DL1.Text == "按学期导出")
            {
                if (DL2.Text == "请选择学期")
                {
                    Alert("请选择学期");
                }
                else
                {//缺少返回空表处理
                    str_sql = "select * from CLASS_INFO,SEM_INFO,COURSE_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='"+accnum+"' and SEM_INFO.id=CLASS_INFO.semesterid and SEM_INFO.name='"+DL2.Text+"';";///////////////////////////////////////////////////////////
                    string column_names = "semesterid,courseid,studentid,mark,state";  //"id,name,tel"是被分解的字符串，用于插入到excel表格中，需要自定义////////////////////////////
                    DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
                }
            }
        }
    }

    protected void DL1_TextChanged(object sender, EventArgs e)
    {
        
        DL2.Visible = false;
        if (DL1.Text == "按学期导出")
        {
            DL2.Text = "请选择学期";
            DL2.Visible = true;
        }
    }

   
    protected void DL3_TextChanged(object sender, EventArgs e)
    {
        DL4.Visible = false;
        if (DL3.Text == "按学期删除")
        {
            DL4.Text = "请选择学期";
            DL4.Visible = true;
        }
    }
    protected void Button2_Click(object sender, EventArgs e)//缺乏空表出错处理
    {
        string sql = "";
        if (DL3.Text == "请选择删除数据")
        {
            Alert("请选择删除数据");
        }
        else
        {
            if (DL3.Text == "全部删除")
            {
                sql = "delete from CLASS_INFO;";
                Execute(sql);
               
            }
            else if (DL3.Text == "按学期删除")
            {
                if (DL4.Text == "请选择学期")
                {
                    Alert("请选择学期");
                }
                else
                {
                    sql = "delete from CLASS_INFO where CLASS_INFO.semesterid in(select SEM_INFO.id from SEM_INFO,CLASS_INFO,COURSE_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='"+accnum+"' and SEM_INFO.id=CLASS_INFO.semesterid and SEM_INFO.name='"+DL4.Text+"');";
                    try
                    {
                        Execute(sql);
                        Alert("删除成功");
                    }
                    catch 
                    {
                       
                    }
                }
            }
        }
        
    }

    void Execute(string sql)
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();
        myConn.Close();
    }
    
    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
}
